# coding:utf-8

import os
import time
import random
from decimal import Decimal
import set_env_path  # 请勿删除

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, create_engine, text, Numeric, Boolean
from sqlalchemy.orm import sessionmaker
import xlrd

Base = declarative_base()


def get_timestamp():
    return int(time.time())


class User(Base):
    __tablename__ = 'user'

    id = Column(Integer, primary_key=True)
    created_at = Column(Integer, nullable=False, default=get_timestamp)
    updated_at = Column(Integer, nullable=False, default=get_timestamp, onupdate=get_timestamp)
    deleted_at = Column(Integer, nullable=False, default=0)

    area_id = Column(Integer, nullable=False, server_default=text("'0'"), default=0)
    im_token = Column(
        String(32, 'utf8mb4_unicode_ci'),
        nullable=False,
        server_default=text("''"),
        default=''
    )
    push_reg_id = Column(
        String(64, 'utf8mb4_unicode_ci'),
        nullable=False,
        server_default=text("''"),
        default=''
    )
    wx_openid = Column(
        String(128, 'utf8mb4_unicode_ci'),
        nullable=False,
        server_default=text("''"),
        default=''
    )
    type = Column(
        Integer, nullable=False, server_default=text("'0'"), default=0)
    level = Column(
        Integer, nullable=False, server_default=text("'0'"), default=0)
    mobile = Column(
        String(16, 'utf8mb4_unicode_ci'),
        nullable=False,
        index=True,
        server_default=text("''"),
        default=''
    )
    nickname = Column(
        String(64, 'utf8mb4_unicode_ci'),
        nullable=False,
        server_default=text("''"),
        default=''
    )
    gender = Column(
        Integer, nullable=False, server_default=text("'0'"), default=0)
    prov = Column(
        String(16, 'utf8mb4_unicode_ci'),
        nullable=False,
        server_default=text("''"),
        default='',
        index=True
    )
    city = Column(
        String(16, 'utf8mb4_unicode_ci'),
        nullable=False,
        server_default=text("''"),
        default=''
    )
    addr = Column(
        String(128, 'utf8mb4_unicode_ci'),
        nullable=False,
        server_default=text("''"),
        default=''
    )
    industry = Column(
        Integer, nullable=False, server_default=text("'0'"), default=0)
    com = Column(
        String(64, 'utf8mb4_unicode_ci'),
        nullable=False,
        server_default=text("''"),
        default=''
    )
    pos = Column(
        String(64, 'utf8mb4_unicode_ci'),
        nullable=False,
        server_default=text("''"),
        default=''
    )
    avatar = Column(
        String(255, 'utf8mb4_unicode_ci'),
        nullable=False,
        server_default=text("''"),
        default=''
    )
    bank_name = Column(
        String(16, 'utf8mb4_unicode_ci'),
        nullable=False,
        server_default=text("''"),
        default=''
    )
    bank_acct_name = Column(
        String(16, 'utf8mb4_unicode_ci'),
        nullable=False,
        server_default=text("''"),
        default=''
    )
    bank_acct_num = Column(
        String(16, 'utf8mb4_unicode_ci'),
        nullable=False,
        server_default=text("''"),
        default=''
    )
    bal = Column(
        Numeric(16, 2), nullable=False,
        server_default=text("'0.00'"),
        default=Decimal('0.00')
    )
    # 0 - 未绑定过微信; 1 - 绑定过微信
    bind_wx_openid = Column(
        Integer, nullable=False, server_default=text("'0'"), default=0)
    no_sms = Column(
        Integer, nullable=False, server_default=text("'0'"), default=0)
    callback_at = Column(
        Integer, nullable=False, server_default=text("'0'"), default=0)
    active = Column(
        Integer, nullable=False, server_default=text("'0'"), default=0)
    is_virt = Column(
        Integer, nullable=False, server_default=text("'0'"), default=0)
    status = Column(
        Integer, nullable=False, server_default=text("'0'"), default=0)
    remark = Column(
        String(256, 'utf8mb4_unicode_ci'),
        nullable=False,
        server_default=text("''"),
        default=''
    )
    # 积分
    credit = Column(Integer, default=0, server_default=text("'0'"))
    # 是否内部用户
    is_internal = Column(Boolean, default=False, server_default=text('FALSE'))


engine = create_engine(
    os.getenv('DATABASE_URL')
    or 'mysql+pymysql://root:@127.0.0.1/wph_www_prod?charset=utf8'
)
Session = sessionmaker(bind=engine)
session = Session()
mobile_suffix_list = set()
mobile_suffix_list.add('')

res = session.execute("""
    select mobile from user where mobile like '3000000%'
""").fetchall()
for each in res:
    mobile_suffix_list.add(each.mobile[-4:])


def get_suffix():
    """
    created by chenhj at 05/02/2018

    """
    suffix = ''
    while suffix in mobile_suffix_list:
        suffix = '{}{}{}{}'.format(
            random.randint(1, 9),
            random.randint(0, 9),
            random.randint(0, 9),
            random.randint(2, 7),
        )
    mobile_suffix_list.add(suffix)
    return suffix


a = ["杨怡伶", "虹利光电", "杨郁婷", "张旺茹", "智通光电", "吴淑霞", "许育海", "赖雅婷", "谢宜恩", "储世昌", "张雅泰", "周左达", "林馨仪", "方联广告", "苏思翰", "赵美绍",
     "黄崇舜", "杨季生", "陈一柏", "杨信宏", "叶忠翰", "林子竹", "黄千倩", "蔡冠宇", "张玉萍", "弘锐科技", "谢伟元", "林婉婷", "刘冠良", "侯盈如", "彤彩广告", "奥维光电",
     "陈雅惠", "叶依婷", "黄美惠", "林宁平", "吴天琪", "周凯翔", "刘竹娥", "陈文军", "明佳广告", "林盈薇", "张凯绍", "伍湖琴", "陈佳霖", "丁志明", "许晓雯", "郑忠琴",
     "杜湖怡", "林怡君", "林慧顺", "赖建谕", "明冠科技", "林晏娥", "冯晴瑄", "孙宣玮", "黄淑中", "黎雅铃", "青橙广告", "涂亭淑", "张喜娟", "林懿名", "蔡嘉莲", "王靖宣",
     "曹雅芬", "吴盈秀", "黄欣洁", "锦彩广告", "梁平宁", "林姿美", "张世竹", "黄诗昀", "侯秉琳", "蔡行均", "华枫广告", "宋瑶帆", "何嘉惠", "翰威广告", "陈玟霞", "黄崇帆",
     "曾台云", "张怡洁", "张永任", "曾怡婷", "许镇霖", "洪伟哲", "林雅婷", "黄雅婷", "明源光电", "许宏伟", "黄佳慧", "袁宜君", "曾珍瑜", "许玉婷", "胜辉广告", "王伟汉",
     "黄冠芸", "张瑞琴", "罗骏英", "蔡宝梅", "魏良桂", "褚彦君", "张怡君", "辉隆光电", "郭家良", "陈培馨", "安琪广告", "王思瑄", "亨利科技", "吴佩容", "黄重佩", "派诺广告",
     "晶裕光电", "李国荣", "刘宗毅", "鸿灿科技", "符静裕", "杨雅玲", "叶郁婷", "吴雅芳", "蔡启昆", "宏泰光电", "欣彩广告", "林雅帆", "李家凡", "林木光", "勤达科技",
     "岑威廷", "林义廷", "仕达广告", "叶百香", "睿思广告", "陈建乐", "潘志婷", "林金士", "裕光光电", "小雅广告", "彭桂欢", "傅正韵", "郑柏宏", "姚乔任", "林政儒",
     "视晟广告", "林玉天", "宏荣光电", "吉福广告", "蔡淑娟", "郑家贤", "涂杰嘉", "林诗婷", "旭彩科技", "侯淑媛", "盛利科技", "许文华", "谢婉婷", "陈玉伯", "陈智杰",
     "亚泰恩科技", "李淑志", "李怡如", "谢秀娟", "程达科技", "林佩君", "林家人", "王思梦", "张重雅", "李宜真", "杨宗仪", "陈国珠", "马翰佩", "刘翠枝", "凯瑞光电", "王怡志",
     "武创科技", "吴承佩", "周淑婷", "蔡孟富", "腾琪广告", "王香泉", "吴慧旺", "王怡婷", "许台珠", "吴佳俐", "杰特莱科技", "李佩阳", "林育廷", "冠申广告", "朗辉光电",
     "陈又木", "德胜广告", "许佩瑜", "张文瑜", "刘少沛", "侯丽萍", "聚彩光电", "王俐睿", "郑宁筠", "许怡如", "蓝中依", "方倩婷", "王凯婷", "冯梦绍", "明翰光电", "谢志成",
     "晶宇科技", "维彩广告", "蔡雅萍", "黄婉玲", "特威光电", "郭茹美", "陈昭辰", "张智翔", "袁孟颖", "潘铭富", "家必儒", "周惠玲", "美视信广告", "黄俊贤", "铭辰光电",
     "王雅婷", "吴致远", "郑彦博", "李美杰", "林文雨", "玖玖广告", "黄琼来", "沙淑君", "郑芝瑶", "杨俊谚", "郑佳玲", "孙思婷", "陈成富", "科达明广告", "李绍凡", "盛辉光电",
     "中胜广告", "罗庭玮", "黄嘉英", "刘柏廷", "蔡宜星", "李美玲", "陈尚紫", "亿星广告", "李筱涵", "黎佳慧", "王佳慧", "陈俐俊", "宋群平", "张慧娟", "黄美隆", "王俊政",
     "金盈玫", "兴利广告", "王美仁", "蔡雅筑", "彭武苹", "杨玲乐", "彩瑞光电", "杨立以", "升美光电", "陈俞士", "鑫达光电", "吴博文", "陈诗发", "谢伟伦", "林佩瑜", "吴佳燕",
     "深圳星河科技", "曾承廷", "徐振霖", "詹晴香", "李智超", "李宛紫", "熊成芬", "沈可欣", "梁琬婷", "方怡婷", "冯孝惠", "达彩光电", "杨庆桂", "博朗科技", "吴景靖",
     "李台隆", "郭兰龙", "莫志维", "黄佩蓉", "郑青心", "邱呈龙", "裴尚广告", "凯得广告", "冯哲玮", "刘士凯", "吴芳仪", "黄虹孝", "林佩珊", "林家祥", "黄怡孜", "宇朗科技",
     "刘家宜", "常淑芳", "洪仁豪", "陈伟广告", "吴冠廷", "张玮季", "郑静怡", "吴柔任", "郭静颖", "博彩广告", "弘联科技", "王石男", "陈静怡", "罗品冰", "铭柯广告", "陈丽恩",
     "蓝惠玲", "胡芝芸", "翔拓科技", "沈建铭", "张冠伶", "张育廷", "李真瑄", "陈彦新", "马健豪", "连长勋", "智彩广告", "杨怡贞", "张凯帆", "洪昱宏", "宏特广告", "潘佑凤",
     "陈智易", "张嘉凡", "陈文海", "周芳仪", "杰朗广告", "吴佩慧", "吴思谕", "陈云松", "杨佳蓉", "邓伊修", "林立琴", "蔡初亚", "李佳仪", "莞通科技", "吴伯奇", "黄冠杰",
     "郭淑珍", "乐美光电", "袁志铭", "吴佩军", "普丽司广告", "赖俊诚", "胡原倩", "陈士杰", "陈冠筠", "谢佳蓉", "赵秀娟", "李怡君", "方昇科技", "黄学荣", "杨耀星", "陈昆坤",
     "苏友柏", "柯建勋", "黄馨贤", "吴杰欣", "林耀韦", "方俊贤", "刘怡淳", "陈青财", "梁山名", "韩佳儒", "张阿行", "金国华", "赖海盛", "李淑华", "巩琼慧", "创合广告",
     "程贵木", "李天柏", "赖怡萱", "顺裕科技", "林静如", "张行原", "齐盛广告", "吴季达", "方明广告", "兴龙广告", "谢隆萍", "韩慈阳", "叶雅婷", "赵羽勋", "宝智广告",
     "何政依", "林依婷", "信莱广告", "蔡伟伦", "戴雅晴", "吴思颖", "李俊依", "王圣政", "林台韦", "吴宗乐", "王明善", "澜源广告", "韩志远", "苏静宜", "李岳秀", "陈石阳",
     "胡玉婷", "邓思涵", "雷德科技", "赖佳慧", "谢宜孝", "陈娇生", "陈世韦", "谢亚依", "吕佳琪", "青谷广告", "聚飞科技", "杨宛蓉", "涂俊达", "陈亭筠", "萧幸康", "优聚广告",
     "郑慧玟", "翁志玮", "吴益莲", "郑昭枝", "梁一瑞", "永达广告", "姜武天", "柯郁翔", "黄金惟；张吉惟", "林国瑞", "林玟书", "林雅南", "江奕云", "刘柏宏", "黎芸贵",
     "捷登广告", "雷进宝", "升达科技", "吴心真", "王美珠", "郭芳天", "李雅惠", "陈文婷", "曹亨广告", "王依婷"]

for nickname in a:
    user = User(
        area_id=440300,
        type=100,
        mobile='3000000{suffix}'.format(suffix=get_suffix()),
        nickname=nickname,
        gender=1,
        prov='广东省',
        city='深圳市',
        addr='',
        is_virt=1,
        credit=-10000,
        is_internal=True
    )
    session.add(user)
session.commit()
session.close()
